package com.example.demo.util;

import com.example.demo.model.Student;
import com.example.demo.model.Teacher;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.Year;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {

    // 创建学生导入模板
    public static byte[] createStudentTemplate() throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("学生导入模板");

        // 创建表头
        Row headerRow = sheet.createRow(0);
        String[] headers = {"学号", "姓名", "学院", "专业", "班级", "入学年份"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            // 设置表头样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            headerStyle.setFont(font);
            cell.setCellStyle(headerStyle);
        }

        // 示例数据行
        Row exampleRow = sheet.createRow(1);
        exampleRow.createCell(0).setCellValue("2022001");
        exampleRow.createCell(1).setCellValue("张三");
        exampleRow.createCell(2).setCellValue("软件学院");
        exampleRow.createCell(3).setCellValue("软件工程");
        exampleRow.createCell(4).setCellValue("软件工程2022级1班");
        exampleRow.createCell(5).setCellValue("2022");

        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();
        return outputStream.toByteArray();
    }

    // 创建教师导入模板
    public static byte[] createTeacherTemplate() throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("教师导入模板");

        // 创建表头
        Row headerRow = sheet.createRow(0);
        String[] headers = {"工号", "姓名", "学院", "职称", "入职日期"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            // 设置表头样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            headerStyle.setFont(font);
            cell.setCellStyle(headerStyle);
        }

        // 示例数据行
        Row exampleRow = sheet.createRow(1);
        exampleRow.createCell(0).setCellValue("T001");
        exampleRow.createCell(1).setCellValue("李四");
        exampleRow.createCell(2).setCellValue("软件学院");
        exampleRow.createCell(3).setCellValue("教授");
        exampleRow.createCell(4).setCellValue("2020-09-01");

        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();
        return outputStream.toByteArray();
    }

    // 导出学生列表到Excel
    public static byte[] exportStudentsToExcel(List<Student> students, String className) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("班级_" + className + "_学生列表");

        // 创建表头
        Row headerRow = sheet.createRow(0);
        String[] headers = {"学号", "姓名", "性别", "学院", "专业", "班级", "入学年份"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            // 设置表头样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            headerStyle.setFont(font);
            cell.setCellStyle(headerStyle);
        }

        // 填充数据
        int rowNum = 1;
        for (Student student : students) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(student.getStudentNo());
            row.createCell(1).setCellValue(student.getName());
            row.createCell(2).setCellValue(student.getGender() != null ? student.getGender() : "");
            row.createCell(3).setCellValue(student.getCollegeName() != null ? student.getCollegeName() : "");
            row.createCell(4).setCellValue(student.getMajorName() != null ? student.getMajorName() : "");
            row.createCell(5).setCellValue(student.getClassName() != null ? student.getClassName() : "");
            row.createCell(6).setCellValue(student.getEnrollmentYear() != null ? student.getEnrollmentYear().toString() : "");
        }

        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();
        return outputStream.toByteArray();
    }

    // 导出教师列表到Excel
    public static byte[] exportTeachersToExcel(List<Teacher> teachers) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("教师列表");

        // 创建表头
        Row headerRow = sheet.createRow(0);
        String[] headers = {"工号", "姓名", "性别", "学院", "职称", "入职日期"};
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            // 设置表头样式
            CellStyle headerStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            headerStyle.setFont(font);
            cell.setCellStyle(headerStyle);
        }

        // 填充数据
        int rowNum = 1;
        for (Teacher teacher : teachers) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(teacher.getTeacherNo());
            row.createCell(1).setCellValue(teacher.getName());
            row.createCell(2).setCellValue(teacher.getGender() != null ? teacher.getGender() : "");
            row.createCell(3).setCellValue(teacher.getCollegeName() != null ? teacher.getCollegeName() : "");
            row.createCell(4).setCellValue(teacher.getTitle() != null ? teacher.getTitle() : "");
            row.createCell(5).setCellValue(teacher.getEmploymentDate() != null ? teacher.getEmploymentDate().toString() : "");
        }

        // 自动调整列宽
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }

        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();
        return outputStream.toByteArray();
    }

    // 解析学生Excel文件
    public static List<StudentData> parseStudentExcel(MultipartFile file) throws IOException {
        List<StudentData> students = new ArrayList<>();
        InputStream inputStream = file.getInputStream();
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        // 从第二行开始读取数据（跳过表头）
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                StudentData studentData = new StudentData();
                
                // 读取学号
                Cell studentNoCell = row.getCell(0);
                if (studentNoCell != null) {
                    studentData.setStudentNo(getCellValueAsString(studentNoCell));
                }

                // 读取姓名
                Cell nameCell = row.getCell(1);
                if (nameCell != null) {
                    studentData.setName(getCellValueAsString(nameCell));
                }

                // 读取学院
                Cell collegeCell = row.getCell(2);
                if (collegeCell != null) {
                    studentData.setCollegeName(getCellValueAsString(collegeCell));
                }

                // 读取专业
                Cell majorCell = row.getCell(3);
                if (majorCell != null) {
                    studentData.setMajorName(getCellValueAsString(majorCell));
                }

                // 读取班级
                Cell classCell = row.getCell(4);
                if (classCell != null) {
                    studentData.setClassName(getCellValueAsString(classCell));
                }

                // 读取入学年份
                Cell enrollmentYearCell = row.getCell(5);
                if (enrollmentYearCell != null) {
                    String yearStr = getCellValueAsString(enrollmentYearCell);
                    if (!yearStr.isEmpty()) {
                        try {
                            studentData.setEnrollmentYear(Year.of(Integer.parseInt(yearStr)));
                        } catch (NumberFormatException e) {
                            // 如果解析失败，保持默认值
                        }
                    }
                }

                students.add(studentData);
            }
        }

        workbook.close();
        inputStream.close();
        return students;
    }

    // 解析教师Excel文件
    public static List<TeacherData> parseTeacherExcel(MultipartFile file) throws IOException {
        List<TeacherData> teachers = new ArrayList<>();
        InputStream inputStream = file.getInputStream();
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        // 从第二行开始读取数据（跳过表头）
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                TeacherData teacherData = new TeacherData();
                
                // 读取工号
                Cell teacherNoCell = row.getCell(0);
                if (teacherNoCell != null) {
                    teacherData.setTeacherNo(getCellValueAsString(teacherNoCell));
                }

                // 读取姓名
                Cell nameCell = row.getCell(1);
                if (nameCell != null) {
                    teacherData.setName(getCellValueAsString(nameCell));
                }

                // 读取学院
                Cell collegeCell = row.getCell(2);
                if (collegeCell != null) {
                    teacherData.setCollegeName(getCellValueAsString(collegeCell));
                }

                // 读取职称
                Cell titleCell = row.getCell(3);
                if (titleCell != null) {
                    teacherData.setTitle(getCellValueAsString(titleCell));
                }

                // 读取入职日期
                Cell employmentDateCell = row.getCell(4);
                if (employmentDateCell != null) {
                    String dateStr = getCellValueAsString(employmentDateCell);
                    if (!dateStr.isEmpty()) {
                        try {
                            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
                            teacherData.setEmploymentDate(LocalDate.parse(dateStr, formatter));
                        } catch (Exception e) {
                            // 如果解析失败，保持默认值
                        }
                    }
                }

                teachers.add(teacherData);
            }
        }

        workbook.close();
        inputStream.close();
        return teachers;
    }

    // 获取单元格值为字符串
    private static String getCellValueAsString(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    // 学生数据传输对象
    public static class StudentData {
        private String studentNo;
        private String name;
        private String collegeName;
        private String majorName;
        private String className;
        private Year enrollmentYear;

        // Getters and Setters
        public String getStudentNo() {
            return studentNo;
        }

        public void setStudentNo(String studentNo) {
            this.studentNo = studentNo;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getCollegeName() {
            return collegeName;
        }

        public void setCollegeName(String collegeName) {
            this.collegeName = collegeName;
        }

        public String getMajorName() {
            return majorName;
        }

        public void setMajorName(String majorName) {
            this.majorName = majorName;
        }

        public String getClassName() {
            return className;
        }

        public void setClassName(String className) {
            this.className = className;
        }

        public Year getEnrollmentYear() {
            return enrollmentYear;
        }

        public void setEnrollmentYear(Year enrollmentYear) {
            this.enrollmentYear = enrollmentYear;
        }
    }

    // 教师数据传输对象
    public static class TeacherData {
        private String teacherNo;
        private String name;
        private String collegeName;
        private String title;
        private LocalDate employmentDate;

        // Getters and Setters
        public String getTeacherNo() {
            return teacherNo;
        }

        public void setTeacherNo(String teacherNo) {
            this.teacherNo = teacherNo;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getCollegeName() {
            return collegeName;
        }

        public void setCollegeName(String collegeName) {
            this.collegeName = collegeName;
        }

        public String getTitle() {
            return title;
        }

        public void setTitle(String title) {
            this.title = title;
        }

        public LocalDate getEmploymentDate() {
            return employmentDate;
        }

        public void setEmploymentDate(LocalDate employmentDate) {
            this.employmentDate = employmentDate;
        }
    }
}